--------------------------------------------------------------
-- dbsetup.sql
--
-- DrawBridge database setup file
-- 
-- Setup the database for the wireless sensor network.  
-- Create the database, called drawbridge, create the roles
-- dbdaemon and dbreader.  dbdaemon will have select privileges
-- on the tables motes, mote_conf, and sensors.  It will have
-- insert priveleges on all the data tables (tables that hold
-- the sensor data).
-- The dbreader role will have select priveleges on all the 
-- tables.
--
-- The tables.sql file will then be called to set up the tables.
----------------------------------------------------------------

CREATE DATABASE drawbridge;

-- Set up users
CREATE USER drbgadmin;
CREATE USER drbgdaemon;
CREATE USER drbgreader;

ALTER USER drbgdaemon PASSWORD 'secret';
ALTER USER drbgreader PASSWORD 'secret';

GRANT ALL PRIVILEGES ON DATABASE drawbridge TO drbgadmin;
ALTER DATABASE drawbridge OWNER TO drbgadmin;


\c drawbridge

SET ROLE drbgadmin;

-- Create tables
\i tables.sql

-- Set permissions

-- Limit number of connections to the database
ALTER DATABASE drawbridge WITH CONNECTION LIMIT 60;

-- Limit who can connect to the database
REVOKE CONNECT ON DATABASE drawbridge FROM PUBLIC RESTRICT;

-- Don't let anyone but admins create tables
REVOKE CREATE ON DATABASE drawbridge FROM PUBLIC RESTRICT;


-- drawbridge database daemon
GRANT CONNECT ON DATABASE drawbridge TO drbgdaemon;

GRANT SELECT ON motes TO drbgdaemon;
GRANT SELECT ON sensors TO drbgdaemon;
GRANT SELECT ON mote_conf TO drbgdaemon;
GRANT SELECT ON accel_x TO drbgdaemon;
GRANT SELECT ON accel_y TO drbgdaemon;
GRANT SELECT ON mag_x TO drbgdaemon;
GRANT SELECT ON mag_y TO drbgdaemon;
GRANT SELECT ON light TO drbgdaemon;
GRANT SELECT ON ir TO drbgdaemon;
GRANT SELECT ON humidity TO drbgdaemon;
GRANT SELECT ON temperature TO drbgdaemon;
GRANT INSERT, UPDATE ON accel_x TO drbgdaemon;
GRANT INSERT, UPDATE ON accel_y TO drbgdaemon;
GRANT INSERT, UPDATE ON mag_x TO drbgdaemon;
GRANT INSERT, UPDATE ON mag_y TO drbgdaemon;
GRANT INSERT, UPDATE ON light TO drbgdaemon;
GRANT INSERT, UPDATE ON ir TO drbgdaemon;
GRANT INSERT, UPDATE ON humidity TO drbgdaemon;
GRANT INSERT, UPDATE ON temperature TO drbgdaemon;
--GRANT DELETE ON accel_x TO drbgdaemon;
--GRANT DELETE ON accel_y TO drbgdaemon;
--GRANT DELETE ON mag_x TO drbgdaemon;
--GRANT DELETE ON mag_y TO drbgdaemon;
--GRANT DELETE ON light TO drbgdaemon;
--GRANT DELETE ON ir TO drbgdaemon;
--GRANT DELETE ON humidity TO drbgdaemon;
--GRANT DELETE ON temperature TO drbgdaemon;


-- drawbridge database reader
GRANT CONNECT ON DATABASE drawbridge TO drbgreader;
GRANT SELECT ON motes TO drbgreader;
GRANT SELECT ON sensors TO drbgreader;
GRANT SELECT ON mote_conf TO drbgreader;
GRANT SELECT ON accel_x TO drbgreader;
GRANT SELECT ON accel_y TO drbgreader;
GRANT SELECT ON mag_x TO drbgreader;
GRANT SELECT ON mag_y TO drbgreader;
GRANT SELECT ON light TO drbgreader;
GRANT SELECT ON ir TO drbgreader;
GRANT SELECT ON humidity TO drbgreader;
GRANT SELECT ON temperature TO drbgreader;

-- Go back to the original user's role
RESET ROLE;